const query = require('./query')

const queryAddressLength = async () => {
  const sql = 'select count(*) as length from address where address_status = 1'
  try {
    const result = await query(sql)
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}

const queryAddressList = async (userId, currentPage, pageSize) => {
  const sql = `select a.address_id, a.address_name,a.address_phone,b.province_name,c.city_name,d.county_name,e.way_name,a.address_detail
  from address a
  join province b
  join city c
  join county d
  join way e
  where a.address_status = 1
  and a.user_id = ?
  and a.province_id = b.province_id
  and a.city_id = c.city_id
  and a.county_id = d.county_id
  and a.way_id = e.way_id
  order by a.address_id asc
  limit ?,?
  `
  try {
    const result = await query(sql, [userId, (currentPage - 1) * pageSize, +pageSize])
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}

const queryProvinceList = async () => {
  const sql = 'select province_id,province_name from province where province_status = 1'
  try {
    const result = await query(sql)
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}

const queryCityList = async (provinceId) => {
  const sql = 'select city_id,city_name from city where city_status = 1 and province_id = ?'
  try {
    const result = await query(sql, provinceId)
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}

const queryCountyList = async (cityId) => {
  const sql = 'select county_id,county_name from county where county_status = 1 and city_id = ?'
  try {
    const result = await query(sql, cityId)
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}

const queryWayList = async (countyId) => {
  const sql = 'select way_id,way_name from way where way_status = 1 and county_id = ?'
  try {
    const result = await query(sql, countyId)
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}

const queryAddressListByWayName = async (wayName) => {
  const sql = `select a.way_id,a.way_name,b.county_name,c.city_name,d.province_name 
  from way a 
  join county b 
  join city c 
  join province d 
  where way_status = 1 
  and way_name like '%${wayName}%' 
  and a.county_id = b.county_id 
  and b.city_id = c.city_id 
  and c.province_id = d.province_id;`
  try {
    const result = await query(sql)
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}
const queryProvinceIdByProvinceName = async (province_name) => {
  const sql = `select province_id from province where province_name = ? and province_status = 1`
  try {
    const result = await query(sql, province_name)
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}
const queryCityIdByCityName = async (city_name) => {
  const sql = `select city_id from city where city_name = ? and city_status = 1`
  try {
    const result = await query(sql, city_name)
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}
const queryCountyIdByCountyName = async (county_name) => {
  const sql = `select county_id from county where county_name = ? and county_status = 1`
  try {
    const result = await query(sql, county_name)
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}
const queryWayIdByWayName = async (way_name) => {
  const sql = `select way_id from way where way_name = ? and way_status = 1`
  try {
    const result = await query(sql, way_name)
    return result
  } catch (error) {
    return Promise.reject(error)
  } Province
}
const insertAddress = async (address, user_id) => {
  const sql = `insert into address(
    address_name,address_phone,address_detail,
    province_id,city_id,county_id,way_id,user_id
  )
  values(?,?,?,?,?,?,?, ?)`
  try {
    const { address_name, address_phone, address_detail, province_id, city_id, county_id, way_id } = address
    const result = await query(sql, [address_name, address_phone, address_detail, province_id, city_id, county_id, way_id, user_id])
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}
const updateAddress = async (address) => {
  const sql = `update address a,
  (select province_id from province where province_name = ?) b,
  (select city_id from city where city_name = ?) c,
  (select county_id from county where county_name = ?) d,
  (select way_id from way where way_name = ?) e
  set 
  a.address_name = ?,
  a.address_phone = ?,
  a.address_detail = ?,
  a.province_id = b.province_id,
  a.city_id = c.city_id,
  a.county_id = d.county_id,
  a.way_id = e.way_id
  where a.address_id = ?
  `
  try {
    const { province_name, city_name, county_name, way_name, address_name, address_phone, address_detail, address_id } = address
    const result = await query(sql, [province_name, city_name, county_name, way_name, address_name, address_phone, address_detail, address_id])
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}
const updateAddressForDelByAddressId = async (address_id) => {
  const sql = `update address set 
  address_status = 0 
  where address_id = ?`
  try {
    const result = await query(sql, Number(address_id))
    console.log(result);
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}
module.exports = {
  queryAddressLength,
  queryAddressList,
  queryProvinceList,
  queryCityList,
  queryCountyList,
  queryWayList,
  queryAddressListByWayName,
  insertAddress,
  updateAddress,
  queryProvinceIdByProvinceName,
  queryCityIdByCityName,
  queryCountyIdByCountyName,
  queryWayIdByWayName,
  updateAddressForDelByAddressId
}